﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
/// Summary description for SanPhamDAO
/// </summary>
public class SanPhamDAO:AbstractDAO
{

    public SanPhamDAO(String strPath)
        : base(strPath)
    {

    }

    ~SanPhamDAO()
    {
        base.Dispose();
    }

    public DataSet SelectSanPhamByLoaiSP_NSX(int loaisp, int nsx)
    {
        DataSet ds;
        try
        {
            ds = new DataSet();
            base.sql = "usp_SelectSanPhamByLoaiSP_NSX";
            base.InitializeCommand();

            base.AddParameter("@idLoaiSP",
            SqlDbType.Int, 10, loaisp);

            base.AddParameter("@idNSX",
            SqlDbType.Int, 10, nsx);

            base.FillDataSet(ref ds, "SelectSanPhamByLoaiSP_NSX");
            return ds;
        }
        catch (System.Exception e)
        {
            throw new Exception(e.Message, e.InnerException);
        }
        finally
        {
            base.CleanUpObject();
        }
    }

    

    public DataSet SelectSanPham(int id)
    {
        DataSet ds;
        try
        {
            ds = new DataSet();
            base.sql = "usp_SelectSanPham";
            base.InitializeCommand();

            base.AddParameter("@id",
            SqlDbType.Int, 10, id);

            base.FillDataSet(ref ds, "SelectSanPham");
            return ds;
        }
        catch (System.Exception e)
        {
            return null;
        }
        finally
        {
            base.CleanUpObject();
        }
    }
    public DataSet SelectSanPhamByGia(long giasan, long giatran, string loaisp)
    {
        DataSet ds;
        try
        {
            ds = new DataSet();
            base.sql = "usp_SelectSanPhamByGia";
            base.InitializeCommand();

            base.AddParameter("@giasan",
            SqlDbType.BigInt, 100, giasan);

            base.AddParameter("@giatran",
            SqlDbType.BigInt, 100, giatran);

            base.AddParameter("@loaisp",
             SqlDbType.NVarChar, 20, loaisp);

            base.FillDataSet(ref ds, "SelectSanPhamByGia");
            return ds;
        }
        catch (System.Exception e)
        {
            return null;
        }
        finally
        {
            base.CleanUpObject();
        }
    }
    public Boolean InsertSanPham(SanPhamDTO sanphamDto)
    {
        Boolean kq;
        try
        {
            base.sql = "usp_InsertSanPham";
            base.InitializeCommand();

            base.AddParameter("@idLoaiSP",
            SqlDbType.Int, 10, sanphamDto.IdLoaiSP);

            base.AddParameter("@idNSX",
            SqlDbType.Int, 10, sanphamDto.IdNSX);

            base.AddParameter("@TenSanPham",
            SqlDbType.NVarChar, 200, sanphamDto.TenSanPham);

            base.AddParameter("@MoTa",
            SqlDbType.NText, 10000, sanphamDto.MoTa);

            base.AddParameter("@Gia",
            SqlDbType.BigInt, 10, sanphamDto.Gia);

            base.AddParameter("@idNhaPhanPhoi",
            SqlDbType.Int, 10, sanphamDto.IdNhaPhanPhoi);

            base.AddParameter("@Diem",
            SqlDbType.Int, 10, sanphamDto.Diem);

            base.AddParameter("@SoLuongTon",
            SqlDbType.Int, 10, sanphamDto.SoLuongTon);

            base.AddParameter("@BaoHanh",
            SqlDbType.NVarChar, 100, sanphamDto.BaoHanh);

            base.AddParameter("@HinhMinhHoa",
            SqlDbType.NVarChar, 100, sanphamDto.HinhMinhHoa);

            base.AddParameterWithDirection("@id",
            SqlDbType.Int, 10);

            kq = ExecuteStoredProcedure();

            sanphamDto.Id = Int32.Parse(base.GetParamValue("@id").ToString());
            
            return kq;
        }
        catch (System.Exception ExceptionErr)
        {
            throw new System.Exception(ExceptionErr.Message,
            ExceptionErr.InnerException);
        }
    }

    public Boolean DeleteSanPham(int id)
    {
        try
        {
            base.sql = "usp_DeleteSanPham";
            base.InitializeCommand();

            base.AddParameter("@id",
            SqlDbType.Int, 10, id);

            return ExecuteStoredProcedure();
        }
        catch (System.Exception ExceptionErr)
        {
            throw new System.Exception(ExceptionErr.Message,
            ExceptionErr.InnerException);
        }
    }

    public Boolean UpdateSanPham(SanPhamDTO sanphamDto, int type)
    {
        Boolean kq;
        try
        {
            base.sql = "usp_UpdateSanPham";
            base.InitializeCommand();

            base.AddParameter("@id",
            SqlDbType.Int, 10, sanphamDto.Id);

            base.AddParameter("@idLoaiSP",
            SqlDbType.Int, 10, sanphamDto.IdLoaiSP);

            base.AddParameter("@idNSX",
            SqlDbType.Int, 10, sanphamDto.IdNSX);

            base.AddParameter("@TenSanPham",
            SqlDbType.NVarChar, 200, sanphamDto.TenSanPham);

            base.AddParameter("@MoTa",
            SqlDbType.NText, 10000, sanphamDto.MoTa);

            base.AddParameter("@Gia",
            SqlDbType.BigInt, 10, sanphamDto.Gia);

            base.AddParameter("@idNhaPhanPhoi",
            SqlDbType.Int, 10, sanphamDto.IdNhaPhanPhoi);

            base.AddParameter("@Diem",
            SqlDbType.Int, 10, sanphamDto.Diem);

            base.AddParameter("@SoLuongTon",
            SqlDbType.Int, 10, sanphamDto.SoLuongTon);

            base.AddParameter("@BaoHanh",
            SqlDbType.NVarChar, 100, sanphamDto.BaoHanh);

            base.AddParameter("@HinhMinhHoa",
            SqlDbType.NVarChar, 100, sanphamDto.HinhMinhHoa);

            base.AddParameter("@type",
            SqlDbType.Int, 10, type);

            return ExecuteStoredProcedure();
        }
        catch (System.Exception ExceptionErr)
        {
            throw new System.Exception(ExceptionErr.Message,
            ExceptionErr.InnerException);
        }
    }

    public Boolean UpdateSoLanXem(int id)
    {
        Boolean kq;
        try
        {
            base.sql = "usp_UpdateSoLanXem";
            base.InitializeCommand();

            base.AddParameter("@id",
            SqlDbType.Int, 10, id);

            kq = ExecuteStoredProcedure();

            return kq;
        }
        catch (System.Exception ExceptionErr)
        {
            throw new System.Exception(ExceptionErr.Message,
            ExceptionErr.InnerException);
        }
    }
    public Boolean UpdateDiemSo(int id, int diem)
    {
        Boolean kq;
        try
        {
            base.sql = "usp_UpdateDiemSo";
            base.InitializeCommand();

            base.AddParameter("@id",
            SqlDbType.Int, 10, id);

            base.AddParameter("@diem",
            SqlDbType.Int, 10, diem);

            kq = ExecuteStoredProcedure();

            return kq;
        }
        catch (System.Exception ExceptionErr)
        {
            throw new System.Exception(ExceptionErr.Message,
            ExceptionErr.InnerException);
        }
    }
    public DataSet SelectSanPhamByLoaiSP_NSX_Paging(int loaisp, int nsx,
        int tranghienthoi, int sosp_moitrang, out int tongsotrang)
    {
        DataSet ds;
        try
        {
            ds = new DataSet();
            base.sql = "usp_SelectSanPhamByLoaiSP_NSX_Paging";
            base.InitializeCommand();

            base.AddParameter("@LoaiSP",
            SqlDbType.Int, 10, loaisp);

            base.AddParameter("@NSX",
            SqlDbType.Int, 10, nsx);

            base.AddParameter("@TrangHienThoi",
            SqlDbType.Int, 10, tranghienthoi);

            base.AddParameter("@SoSPMoiTrang",
            SqlDbType.Int, 10, sosp_moitrang);

            base.AddParameterWithDirection("@TongSoSP",
            SqlDbType.Int, 10);

            base.FillDataSet(ref ds, "SelectSanPhamByLoaiSP_NSX_Paging");

            int sosp = Int32.Parse(base.GetParamValue("@TongSoSP").ToString());

            tongsotrang = (int)Math.Ceiling((double)sosp /
                        (double)sosp_moitrang);

            return ds;
        }
        catch (System.Exception e)
        {
            throw new Exception(e.Message, e.InnerException);
        }
        finally
        {
            base.CleanUpObject();
        }
    }
    public DataSet TimKiemSanPham(SanPhamCRT sanpham)
    {
        DataSet ds;
        try
        {
            ds = new DataSet();
            string loaisp = " 1=1 ";
            string nsx = " 1=1 ";
            string tukhoa = " 1=1 ";
            string giatu = " 1=1 ";
            string den = " 1=1 ";

            if (sanpham.Loaisp != 0)
                loaisp = " idLoaiSP = @idLoaiSP ";

            if (sanpham.Nsx != 0)
                nsx = " idNSX = @idNSX ";

            if (sanpham.Tukhoa != "")
                tukhoa = " (TenSanPham LIKE '%' + @tukhoa + '%' OR sp.MoTa LIKE '%' + @tukhoa + '%') ";

            if (sanpham.Giatu != 0)
                giatu = " Gia >= @giatu ";

            if (sanpham.Den != 0)
                den = " Gia <= @den ";

            string strWhere = " WHERE ";
            strWhere += loaisp;
            strWhere += " AND " + nsx;
            strWhere += " AND " + tukhoa;
            strWhere += " AND " + giatu;
            strWhere += " AND " + den;

            base.sql = "SELECT sp.*, TenNSX, TenLoaiSanPham, TenNhaPhanPhoi ";
            base.sql += (" FROM SAN_PHAM as sp, NHA_SAN_XUAT as nsx, LOAI_SAN_PHAM as lsp, NHA_PHAN_PHOI as npp ");
            base.sql += (strWhere + " AND sp.idLoaiSP = lsp.id and sp.idNSX = nsx.id and sp.idNhaPhanPhoi = npp.id ");

            base.InitializeCommand();

            if (sanpham.Loaisp != 0)
                base.AddParameter("@idLoaiSP", SqlDbType.Int, 10, sanpham.Loaisp);

            if (sanpham.Nsx != 0)
                base.AddParameter("@idNSX", SqlDbType.Int, 10, sanpham.Nsx);

            if (sanpham.Tukhoa != "")
                base.AddParameter("@tukhoa", SqlDbType.NVarChar, 100, sanpham.Tukhoa.Trim());

            if (sanpham.Giatu != 0)
                base.AddParameter("@giatu", SqlDbType.BigInt, 100, sanpham.Giatu);

            if (sanpham.Den != 0)
                base.AddParameter("@den", SqlDbType.BigInt, 100, sanpham.Den);

            base.FillDataSet(ref ds, "TimKiemSanPham");

            return ds;
        }
        catch (System.Exception ExceptionErr)
        {
            throw new System.Exception(ExceptionErr.Message,
            ExceptionErr.InnerException);
        }
        finally
        {
            base.CleanUpObject();
        }
    }
    public DataSet TimKiemSanPham_Paging(SanPhamCRT sanpham, int tranghienthoi, int sosp_moitrang)
    {
        DataSet ds;
        try
        {
            ds = new DataSet();
            string loaisp = " 1=1 ";
            string nsx = " 1=1 ";
            string tukhoa = " 1=1 ";
            string giatu = " 1=1 ";
            string den = " 1=1 ";

            if (sanpham.Loaisp != 0)
                loaisp = " sp.idLoaiSP = @idLoaiSP ";

            if (sanpham.Nsx != 0)
                nsx = " sp.idNSX = @idNSX ";

            if (sanpham.Tukhoa != "")
                tukhoa = " (sp.TenSanPham LIKE '%' + @tukhoa + '%' OR sp.MoTa LIKE '%' + @tukhoa + '%') ";

            if (sanpham.Giatu != 0)
                giatu = " sp.Gia >= @giatu ";

            if (sanpham.Den != 0)
                den = " sp.Gia <= @den ";

            string strWhere = "";
            strWhere += " AND " + loaisp;
            strWhere += " AND " + nsx;
            strWhere += " AND " + tukhoa;
            strWhere += " AND " + giatu;
            strWhere += " AND " + den;
           
            base.sql = "SELECT p.RowNumber, p.ID, p.idLoaiSP, p.idNSX, p.TenSanPham, p.MoTa, p.Gia, p.idNhaPhanPhoi, ";
            base.sql += "p.Diem, p.SoLuongTon, p.NgayThemSP, p.BaoHanh, p.HinhMinhHoa, ";
            base.sql += "p.SoLanXem, p.TenNSX, p.TenLoaiSanPham, p.TenNhaPhanPhoi ";
            base.sql += "FROM (select ROW_NUMBER() OVER (ORDER BY sp.id) RowNumber, sp.id ID, ";
            base.sql += "idLoaiSP, idNSX, TenSanPham, sp.MoTa MoTa, Gia, idNhaPhanPhoi, Diem, ";
            base.sql += "SoLuongTon, NgayThemSP, BaoHanh, HinhMinhHoa, SoLanXem, ";
            base.sql += "TenNSX, TenLoaiSanPham, TenNhaPhanPhoi	";
            base.sql += "from SAN_PHAM as sp, NHA_SAN_XUAT as nsx, LOAI_SAN_PHAM as lsp, ";
            base.sql += "NHA_PHAN_PHOI as npp ";
            base.sql += "where sp.idLoaiSP = lsp.id and sp.idNSX = nsx.id and sp.idNhaPhanPhoi = npp.id ";
            base.sql += strWhere;
            base.sql += ") p ";
            base.sql += " WHERE p.RowNumber > (@TrangHienThoi - 1) * @SoSPMoiTrang ";
            base.sql += " AND p.RowNumber <= @TrangHienThoi * @SoSPMoiTrang ";



            base.InitializeCommand();

            if (sanpham.Loaisp != 0)
                base.AddParameter("@idLoaiSP", SqlDbType.Int, 10, sanpham.Loaisp);

            if (sanpham.Nsx != 0)
                base.AddParameter("@idNSX", SqlDbType.Int, 10, sanpham.Nsx);

            if (sanpham.Tukhoa != "")
                base.AddParameter("@tukhoa", SqlDbType.NVarChar, 100, sanpham.Tukhoa.Trim());

            if (sanpham.Giatu != 0)
                base.AddParameter("@giatu", SqlDbType.BigInt, 100, sanpham.Giatu);

            if (sanpham.Den != 0)
                base.AddParameter("@den", SqlDbType.BigInt, 100, sanpham.Den);

            base.AddParameter("@TrangHienThoi",
            SqlDbType.Int, 10, tranghienthoi);

            base.AddParameter("@SoSPMoiTrang",
            SqlDbType.Int, 10, sosp_moitrang);

            base.FillDataSet(ref ds, "TimKiemSanPham_Paging");

            return ds;
        }
        catch (System.Exception ExceptionErr)
        {
            throw new System.Exception(ExceptionErr.Message,
            ExceptionErr.InnerException);
        }
        finally
        {
            base.CleanUpObject();
        }
    }
}
